# -*- coding: utf-8 -*-
import datetime
import json
import re
from decimal import Decimal
from io import BytesIO

import numpy as np
import pandas as pd
import requests
import scrapy
from loguru import logger

from apps.listed_company.listed_company.items import NetMoneySupply, NetCreditFundsSummary, NetAggregateFinancingFlow, NetAggregateFinancingStock, NetAggregateFinancingFlowByProvince
from components.config import NET_ROBOT_MYSQL_CONFIG
from utils.db.mysqldb import MysqlDB


class AggregateFinancingFlowSpider(scrapy.Spider):
    listed_exchange = '中国人民银行'
    name = 'aggregate_financing_flow'
    headers = {
        "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7",
        "Accept-Language": "zh,zh-TW;q=0.9,en-US;q=0.8,en;q=0.7,zh-CN;q=0.6",
        "Cache-Control": "max-age=0",
        "Proxy-Connection": "keep-alive",
        "Referer": "http://www.pbc.gov.cn/diaochatongjisi/116219/index.html",
        "Upgrade-Insecure-Requests": "1",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36"
    }
    custom_settings = {
        "DUPEFILTER_DEBUG": True,
        'DUPEFILTER_CLASS': 'scrapy.dupefilters.BaseDupeFilter'
    }

    def start_requests(self):
        url = "http://www.pbc.gov.cn/diaochatongjisi/116219/116319/index.html"
        yield scrapy.Request(url, callback=self.parse_list, headers=self.headers)

    def parse_list(self, response, **kwargs):

        year_list = response.xpath('//*[@id="r_con"]//div[@class="wengao2"]')
        year_data = {i.xpath('.//a/text()').get(): i.xpath('.//a/@href').get() for i in year_list}

        for year, href in year_data.items():
            if '2015年统计数据' <= year <= '2024年统计数据':
                url = f'http://www.pbc.gov.cn{href}'
                yield scrapy.Request(url, callback=self.parse_list2, headers=self.headers, cb_kwargs={"year": year})

    def parse_list2(self, response, **kwargs):
        href = re.findall(r'<a href="(.*?)" class="lan14cu">社会融资规模', response.text)[0]

        yield scrapy.Request(f'http://www.pbc.gov.cn{href}', callback=self.parse_list3, headers=self.headers, cb_kwargs=kwargs)

    def parse_list3(self, response, **kwargs):
        year = kwargs.get('year')
        old = '2012' in year or '2013' in year or '2014' in year
        if old:
            trs = response.xpath('//*[@id="con"]//table[@class]//tr')
        else:
            trs = response.xpath('//*[@id="con"]//table[@class="a2015"]//tr')
        for tr in trs:
            if ('社会融资规模增量统计表' in tr.xpath('string(.)').get() or '社会融资规模统计表' in tr.xpath('string(.)').get()) and '地区' not in tr.xpath('string(.)').get():
                hrefs = tr.xpath('.//a/@href').getall()
                for href in hrefs:
                    if 'htm' in href:
                        if 'http://www.pbc.gov.cn' not in href:
                            href = f'http://www.pbc.gov.cn{href}'
                        yield scrapy.Request(href, callback=self.parse_list4, headers=self.headers, cb_kwargs=kwargs)
            elif ('社会融资规模存量统计表' in tr.xpath('string(.)').get()) and '地区' not in tr.xpath('string(.)').get():
                hrefs = tr.xpath('.//a/@href').getall()
                for href in hrefs:
                    if 'htm' in href:
                        if 'http://www.pbc.gov.cn' not in href:
                            href = f'http://www.pbc.gov.cn{href}'
                        yield scrapy.Request(href, callback=self.parse_list5, headers=self.headers, cb_kwargs=kwargs)
            elif ('地区社会融资规模增量统计表' in tr.xpath('string(.)').get()):
                aaa = tr.xpath('.//a')
                for a in aaa:
                    href = a.xpath('./@href').get()
                    Q = a.xpath('./text()').get()
                    if href and 'htm' in href:
                        if 'http://www.pbc.gov.cn' not in href:
                            href = f'http://www.pbc.gov.cn{href}'
                        yield scrapy.Request(href, callback=self.parse_list6, headers=self.headers, cb_kwargs={**kwargs, "Q": Q})

    def parse_list4(self, response, **kwargs):
        year = kwargs.get('year')
        if '数据尚未发布' in response.text:
            logger.info('数据尚未发布')
            return
        df = pd.read_html(response.text)[0]
        if '2012' in year:
            df = df.iloc[4:14].T
            df.columns = range(len(df.columns))
        else:
            df = df.iloc[7:19]
        # 将第一行作为表头（列名）
        # df.columns = df.iloc[0]
        # 将第一列作为行索引（列头）
        # df.set_index(df.columns[0], inplace=True)
        lines = df.to_dict('records')
        for data in lines:
            if pd.isna(data[0]):
                continue
            if '20' not in str(data[0]):
                continue
            if '2013' in year or '2014' in year or '2015' in year or '2016' in year or '2017' in year:
                item = NetAggregateFinancingFlow(**{
                    'time': data[0].replace('.', ''),
                    'afre_flow': Decimal(data[1]),  # 社会融资规模增量
                    'rmb_loans': Decimal(data[2]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[3]),  # 外币贷款
                    'entrusted_loans': Decimal(data[4]),  # 委托贷款
                    'trust_loans': Decimal(data[5]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[6]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[7]),  # 企业债券
                    # 'government_bonds': Decimal(data[8]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[8]),  # 非金融企业境内股票融资
                    # 'asset_backed_securities_of_depository_f  inancial_institutions': Decimal(data[10]),  # 存款类金融机构资产支持证券
                    # 'loans_written_off': Decimal(data[11]),  # 贷款核销
                })
            elif '2018' in year:
                item = NetAggregateFinancingFlow(**{
                    'time': data[0].replace('.', ''),
                    'afre_flow': Decimal(data[1]),  # 社会融资规模增量
                    'rmb_loans': Decimal(data[2]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[3]),  # 外币贷款
                    'entrusted_loans': Decimal(data[4]),  # 委托贷款
                    'trust_loans': Decimal(data[5]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[6]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[7]),  # 企业债券
                    'government_bonds': Decimal(data[8]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[9]),  # 非金融企业境内股票融资
                    # 'asset_backed_securities_of_depository_f  inancial_institutions': Decimal(data[10]),  # 存款类金融机构资产支持证券
                    # 'loans_written_off': Decimal(data[11]),  # 贷款核销
                })
            elif '2012' in year:
                item = NetAggregateFinancingFlow(**{
                    'time': data[0].replace('.', ''),
                    'afre_flow': Decimal(data[2]),  # 社会融资规模增量
                    'rmb_loans': Decimal(data[3]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[4]),  # 外币贷款
                    'entrusted_loans': Decimal(data[5]),  # 委托贷款
                    'trust_loans': Decimal(data[6]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[7]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[8]),  # 企业债券
                    # 'government_bonds': Decimal(data[8]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[9]),  # 非金融企业境内股票融资
                    # 'asset_backed_securities_of_depository_f  inancial_institutions': Decimal(data[10]),  # 存款类金融机构资产支持证券
                    # 'loans_written_off': Decimal(data[11]),  # 贷款核销
                })
            else:

                item = NetAggregateFinancingFlow(**{
                    'time': data[0].replace('.', ''),
                    'afre_flow': Decimal(data[1]),  # 社会融资规模增量
                    'rmb_loans': Decimal(data[2]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[3]),  # 外币贷款
                    'entrusted_loans': Decimal(data[4]),  # 委托贷款
                    'trust_loans': Decimal(data[5]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[6]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[7]),  # 企业债券
                    'government_bonds': Decimal(data[8]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[9]),  # 非金融企业境内股票融资
                    'asset_backed_securities_by_depository_institutions': Decimal(data[10]),  # 存款类金融机构资产支持证券
                    'loans_written_off': Decimal(data[11]),  # 贷款核销
                })
            # print(item)
            yield item

    def parse_list5(self, response, **kwargs):
        year = kwargs.get('year')
        if '数据尚未发布' in response.text:
            logger.info('数据尚未发布')
            return
        df = pd.read_html(response.text)[0]
        if '2019' in year:
            df = df.iloc[4:30].T
            df.columns = range(len(df.columns))
        elif '2020' in year or '2021' in year or '2022' in year or '2023' in year:
            df = df.iloc[4:19].T
            df = df.drop(7, axis=1)
            df.columns = range(len(df.columns))
        else:
            df = df.iloc[4:18].T
            df.columns = range(len(df.columns))

        # 将第一行作为表头（列名）
        # df.columns = df.iloc[0]
        # 将第一列作为行索引（列头）
        # df.set_index(df.columns[0], inplace=True)
        lines = df.to_dict('records')
        for data in lines:
            if pd.isna(data[0]):
                continue
            if '20' not in str(data[0]):
                continue
            if '2015' in year or '2016' in year or '2017' in year:
                item = NetAggregateFinancingStock(**{
                    'time': ''.join([i.rjust(2, '0') for i in data[0].split('.')]),  # 时间
                    'type': 1 if '存量' in data[1] else 2,  # 1代表存量值（万亿人民币），2代表同比增速%。
                    'afre_stock': Decimal(data[4]),  # 社会融资规模存量
                    'rmb_loans': Decimal(data[5]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[6]),  # 外币贷款
                    'entrusted_loans': Decimal(data[7]),  # 委托贷款
                    'trust_loans': Decimal(data[8]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[9]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[10]),  # 企业债券
                    # 'government_bonds': Decimal(data[10]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[11]),  # 非金融企业境内股票融资
                    # 'asset_backed_securities_by_depository_institutions': Decimal(data[12]),  # 存款类金融机构资产支持证券
                    # 'loans_written_off': Decimal(data[13]),  # 贷款核销
                })
            elif '2018' in year:
                item = NetAggregateFinancingStock(**{
                    'time': ''.join([i.rjust(2, '0') for i in data[0].split('.')]),  # 时间
                    'type': 1 if '存量' in data[1] else 2,  # 1代表存量值（万亿人民币），2代表同比增速%。
                    'afre_stock': Decimal(data[4]),  # 社会融资规模存量
                    'rmb_loans': Decimal(data[5]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[6]),  # 外币贷款
                    'entrusted_loans': Decimal(data[7]),  # 委托贷款
                    'trust_loans': Decimal(data[8]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[9]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[10]),  # 企业债券
                    'government_bonds': Decimal(data[11]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[12]),  # 非金融企业境内股票融资
                    # 'asset_backed_securities_by_depository_institutions': Decimal(data[12]),  # 存款类金融机构资产支持证券
                    # 'loans_written_off': Decimal(data[13]),  # 贷款核销
                })
            elif '2019' in year:
                item = NetAggregateFinancingStock(**{
                    'time': ''.join([i.rjust(2, '0') for i in data[0].split('.')]),  # 时间
                    'type': 1 if '存量' in data[1] else 2,  # 1代表存量值（万亿人民币），2代表同比增速%。
                    'afre_stock': Decimal(data[4]),  # 社会融资规模存量
                    'rmb_loans': Decimal(data[6]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[8]),  # 外币贷款
                    'entrusted_loans': Decimal(data[10]),  # 委托贷款
                    'trust_loans': Decimal(data[12]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[14]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[16]),  # 企业债券
                    'government_bonds': Decimal(data[18]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[20]),  # 非金融企业境内股票融资
                    'asset_backed_securities_by_depository_institutions': Decimal(data[22]),  # 存款类金融机构资产支持证券
                    'loans_written_off': Decimal(data[24]),  # 贷款核销
                })
            else:
                item = NetAggregateFinancingStock(**{
                    'time': ''.join([i.rjust(2, '0') for i in data[0].split('.')]),  # 时间
                    'type': 1 if '存量' in data[1] else 2,  # 1代表存量值（万亿人民币），2代表同比增速%。
                    'afre_stock': Decimal(data[3]),  # 社会融资规模存量
                    'rmb_loans': Decimal(data[4]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[5]),  # 外币贷款
                    'entrusted_loans': Decimal(data[6]),  # 委托贷款
                    'trust_loans': Decimal(data[7]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[8]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[9]),  # 企业债券
                    'government_bonds': Decimal(data[10]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[11]),  # 非金融企业境内股票融资
                    'asset_backed_securities_by_depository_institutions': Decimal(data[12]),  # 存款类金融机构资产支持证券
                    'loans_written_off': Decimal(data[13]),  # 贷款核销
                })
            # print(item)
            yield item

    def parse_list6(self, response, **kwargs):
        year = kwargs.get('year')[:4]
        Q = kwargs.get('Q')
        if '数据尚未发布' in response.text:
            logger.info('数据尚未发布')
            return
        xlsx_url = re.findall(r'附件.*?<a href="(.*?)"', response.text)[0]
        if 'http://www.pbc.gov.cn' not in xlsx_url:
            xlsx_url = f'http://www.pbc.gov.cn{xlsx_url}'

        # 发送请求并处理重定向
        resp = requests.get(xlsx_url)
        # 用 pandas 读取内容
        df = pd.read_excel(BytesIO(resp.content))

        df = df.iloc[8:39]
        df.columns = range(len(df.columns))
        if pd.isna(df.iloc[0, 0]):
            df = df.drop(0, axis=1)
            df.columns = range(len(df.columns))
        # 将第一行作为表头（列名）
        # df.columns = df.iloc[0]
        # 将第一列作为行索引（列头）
        # df.set_index(df.columns[0], inplace=True)
        lines = df.to_dict('records')
        time = f"{year}{Q}"  # 时间

        for data in lines:
            if '2015' in time or '2016' in time or '2017' in time or '2018Q1' in time or '2018Q2' in time:
                item = NetAggregateFinancingFlowByProvince(**{
                    'time': time,  # 时间
                    'province': data[0].split()[0],  # 省份
                    'afre_flow': Decimal(data[1]),  # 地区社会融资规模增量
                    'rmb_loans': Decimal(data[2]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[3]),  # 外币贷款
                    'entrusted_loans': Decimal(data[4]),  # 委托贷款
                    'trust_loans': Decimal(data[5]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[6]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[7]),  # 企业债券
                    # 'government_bonds': Decimal(data[8]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[8]),  # 非金融企业境内股票融资
                })
            else:
                item = NetAggregateFinancingFlowByProvince(**{
                    'time': time,  # 时间
                    'province': data[0].split()[0],  # 省份
                    'afre_flow': Decimal(data[1]),  # 地区社会融资规模增量
                    'rmb_loans': Decimal(data[2]),  # 人民币贷款
                    'foreign_currency_denominated_loans': Decimal(data[3]),  # 外币贷款
                    'entrusted_loans': Decimal(data[4]),  # 委托贷款
                    'trust_loans': Decimal(data[5]),  # 信托贷款
                    'undiscounted_bankers_acceptances': Decimal(data[6]),  # 未贴现银行承兑汇票
                    'net_financing_of_corporate_bonds': Decimal(data[7]),  # 企业债券
                    'government_bonds': Decimal(data[8]),  # 政府债券
                    'equity_financing_by_non_financial_enterprises': Decimal(data[9]),  # 非金融企业境内股票融资
                })
            # print(item)
            yield item


if __name__ == "__main__":
    from scrapy import cmdline

    cmdline.execute("scrapy crawl aggregate_financing_flow".split())
